package com.blackcat.recode.dao;

import com.blackcat.recode.entity.Customer;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.blackcat.recode.entity.DeployVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;
/**
 * <p> 客户信息表 Mapper 接口
 * @author blackcat 2024-09-10
 */
public interface CustomerMapper extends BaseMapper<Customer> {

    /**
     * 描述 :   查询客户的部署信息
     * @author : zhangdahui 2025/2/20 下午5:42
     * @param customerId  客户ID
    */
    @Select("SELECT  s.service_name, b.branch_name, b.branch_explain , ur.version_no,DATE(dr.create_time) AS deploy_time\n" +
            "FROM deploy_recode dr\n" +
            "JOIN update_recode ur ON dr.update_id = ur.id\n" +
            "JOIN branch b ON ur.branch_id = b.id\n" +
            "JOIN service s ON b.service_id = s.id\n" +
            "JOIN (\n" +
            "    SELECT dr.customer_id, s.id AS service_id, MAX(DATE(dr.create_time)) AS latest_deploy_time\n" +
            "    FROM deploy_recode dr\n" +
            "    JOIN update_recode ur ON dr.update_id = ur.id\n" +
            "    JOIN branch b ON ur.branch_id = b.id\n" +
            "    JOIN service s ON b.service_id = s.id\n" +
            "    WHERE dr.customer_id = #{customerId}\n" +
            "    GROUP BY dr.customer_id, s.id\n" +
            ") latest_deploys \n" +
            "ON s.id = latest_deploys.service_id AND DATE(dr.create_time) = latest_deploys.latest_deploy_time\n" +
            "WHERE dr.customer_id = #{customerId}\n" +
            "ORDER BY s.service_name")
    List<DeployVo> selectDeployList(String customerId);
}
